/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.kallsonys.dao;

import com.kallsonys.business.KallSonysFacade;
import com.kallsonys.object.Address;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class AddressDao {

    private static String sqlCreate = "{ call PKG_CRUDS.CREATE_ADDRESS (?, ?, ?, ? ,? ,? ,?) }";
    private static String sqlUpdate = "{ call PKG_CRUDS.UPDATE_ADDRESS (?, ?, ?, ? ,? ,? ,?) }";
    private static String sqlGetById = "{ call PKG_CRUDS.GET_ADDRESS (?, ? ) }";
    private DataSource ds;
    private KallSonysFacade localFacade;

    public AddressDao(KallSonysFacade localFacade) {
        this.ds = localFacade.getSharedDs();
        this.localFacade = localFacade;
    }

    /**
     * create
     * @param address
     * @return
     * @throws java.sql.SQLException
     */
    public int create(Address address) throws SQLException, Exception {
        CallableStatement stmt = null;
        Connection con = null;
        int respuesta = 0;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(sqlCreate);

            stmt.setInt(1, address.getIdCity());
            stmt.setLong(2, address.getIdCustomer());
            stmt.setString(3, address.getStreet());
            stmt.setInt(4, address.getType());
            stmt.setString(5, address.getTown());
            stmt.setString(6, address.getPhoneNumber());
            stmt.registerOutParameter(7, OracleTypes.INTEGER);
            stmt.execute();

            respuesta = stmt.getInt(7);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
        return respuesta;
    }

    /**
     * update
     * @param City
     * @throws java.sql.SQLException
     */
    public void update(Address address) throws SQLException, Exception {
        CallableStatement stmt = null;
        Connection con = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(sqlUpdate);
            stmt.setLong(1, address.getId());
            stmt.setInt(2, address.getIdCity());
            stmt.setLong(3, address.getIdCustomer());
            stmt.setString(4, address.getStreet());
            stmt.setInt(5, address.getType());
            stmt.setString(6, address.getTown());
            stmt.setString(7, address.getPhoneNumber());

            stmt.execute();

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
    }

    /**
     * getById
     * @param id
     * @return
     */
    public Address getById(int id) throws SQLException, Exception {
        Address retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(sqlGetById);

            stmt.setInt(1, id);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {
                    retorno = new Address();
                    retorno.setId(rs.getLong("ADDRESSID"));
                    retorno.setIdCity(rs.getInt("CITYID"));
                    retorno.setIdCustomer(rs.getInt("CUSTOMERID"));
                    retorno.setPhoneNumber(rs.getString("ADDRESSPHONENUMBER"));
                    retorno.setStreet(rs.getString("ADDRESSSTREET"));
                    retorno.setTown(rs.getString("ADDRESSTOWN"));
                    retorno.setType(rs.getInt("ADDRESSTYPE"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
}
